package com.unitd.frame.mybatis.plugin.pagination.helper;

import com.unitd.frame.comm.utils.StringUtils;
import com.unitd.frame.mybatis.plugin.pagination.comm.DbTypeEnum;
import com.unitd.frame.mybatis.plugin.pagination.comm.PageParams;

/**
 * @desc 自定义的分页处理工具类, 用于根据不同的数据库类型, 进行不同的数据库分页处理
 * @filename PageSqlUtils.java
 * @copyright www.unitd.com
 * @author Hudan
 * @version 1.0
 * @date 2017/2/5
 */
public class PageSqlUtils {

	private static final String REGEX_N_T_S = "\\n+|\\t+\\s{2,}";

	private static final String PAGE_SIZE_PLACEHOLDER = "#{pageSize}";

	private static final String OFFSET_PLACEHOLDER = "#{offset}";

	private static final String SQL_SELECT_PATTERN = "(select|SELECT).*?(?=f|F)";

	private static final String SQL_ORDER_PATTERN = "(order|ORDER)\\s+(by|BY)";

	private static final String SQL_COUNT_PREFIX = "SELECT count(1) ";

	/**
	 * @desc 根据数据库类型,创建分页查询SQL
	 * @param dbType 数据库类型
	 * @param sql 分页查询SQL
	 * @return String
	 */
	public static String getLimitSQL(DbTypeEnum dbType, String sql) {
		return String.format(dbType.getTemplate(), sql);
	}

	/**
	 * @desc 根据数据库类型,创建分页查询SQL
	 * @param dbType 数据库类型
	 * @param sql 分页查询SQL
	 * @param pageParams 分页参数
	 * @return String
	 */
	public static String getLimitSQL(DbTypeEnum dbType, String sql, PageParams pageParams) {
		return getLimitSQL(dbType, sql)
				.replace(OFFSET_PLACEHOLDER, String.valueOf(pageParams.getOffset()))
				.replace(PAGE_SIZE_PLACEHOLDER, String.valueOf(pageParams.getPageSize()))
				.replaceAll(REGEX_N_T_S, StringUtils.SPACE);
	}

	/**
	 * @desc 根据SQL创建count SQL
	 * @param sql count SQL
	 * @return String
	 */
	public static String getCountSql(String sql) {
		sql = sql.replaceAll(REGEX_N_T_S, StringUtils.SPACE).split(SQL_ORDER_PATTERN)[0];
		return sql.replaceFirst(SQL_SELECT_PATTERN, SQL_COUNT_PREFIX);
	}

	public static void main(String[] args) {
		String sql = "select a.* from audited_policy a where 1=1 \n\t   \n\t   \n\t   \n\t   \n\t   and title like CONCAT('%',?,'%')   \n\t   \n\t   \n\t   \n\t   \n\t   \n\t   \n\t    \n\t   \n\t  order by updated_at desc";

		System.out.println(">>>>" + getCountSql(sql));
		System.out.println(">>>>" + getLimitSQL(DbTypeEnum.MYSQL, sql, new PageParams()));

		System.out.println("------------");
		sql = "select a.* from audited_policy a where 1=1 \n and EXISTS (select 1 from policy_tag tag where tag.id in (select tag_id from policy_r_tag rtag where rtag.policy_id= a.id) and tag.name like CONCAT('%',?,'%') ) order by updated_at desc";

		System.out.println(">>>>" + getCountSql(sql));
		System.out.println(">>>>" + getLimitSQL(DbTypeEnum.MYSQL, sql, new PageParams()));
	}
}